﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;

namespace ExcelSax {
    public class ExcelSaxDataReader : IDataReader, IEnumerable<Object> {                        
        public static readonly DateTime BaseDate = new DateTime(1899, 12, 30);

        /// <summary>
        /// La capacité interne préallouée. Cela correspond au nombre de colonnes que peut contenir la feuille excel sans provoquer de réallocation de mémoire.
        /// Par défaut 1000 colonnes sont supportées.
        /// </summary>
        public static Int32 RowCapacity = 1000;

        #region Private

        private Boolean _isClosed = false;
        private Boolean _isDisposed = false;

        private ExcelSaxConnection _esConnection;

        private OpenXmlReader _oxReader = null;

        /// <summary>
        /// Liste utilisée pour implémenter le this[] de IDataReader
        /// </summary>
        private List<Object> _xlsxRow = new List<Object>(ExcelSaxDataReader.RowCapacity);
        /// <summary>
        /// Variable interne de mesure du numéro de la ligne courrante
        /// </summary>
        private Int32 _ncLine = -1;
        /// <summary>
        /// Variable interne de mesure du numéro Excel de la ligne courrante
        /// </summary>
        private Int32 _ncExcelLine = -1;
        /// <summary>
        /// gestion de la longeur maximale de la ligne
        /// </summary>
        private Int32 _rowMaxLength = 0;
        private Int32 _currentRowMaxLength = 0;        

        #endregion                      
        
        #region Properties

        /// <summary>
        /// Numéro (base 1) de la ligne courrante
        /// </summary>
        public Int32 NumberOfCurrentLine {
            get { return _ncLine + 1; }
        }

        /// <summary>
        /// Numéro (base 1) de la ligne Excel courrante
        /// </summary>
        public Int32 NumberOfCurrentExcelLine {
            get { return _ncExcelLine + 1; }
        }        

        #endregion

        #region Birth

        internal ExcelSaxDataReader(ExcelSaxConnection exConn, String xlsxSheetName) {
            _esConnection = exConn;
            exConn.Open();
            WorkbookPart workbookPart = exConn.Document.WorkbookPart;
            // ----------------------------------
            // recherche de la feuille demandée
            ExcelSax.Sheet ess = (from s in exConn.Sheets where s.Name == xlsxSheetName select s).FirstOrDefault();
            if (ess != null) {                
                WorksheetPart wkp = (WorksheetPart)workbookPart.GetPartById(ess.Id);
                _oxReader = OpenXmlReader.Create(wkp);
                while (_oxReader.Read()) {
                    if (_oxReader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Row)) {
                        break;
                    }
                }
            } else {
                throw new Exception(String.Format("no such sheet", xlsxSheetName));
            }                      
        }

        #endregion

        #region IDataReader

            #region Methods

        public void Close() {
            if ( !_isClosed ) {
                if (_oxReader != null) {
                    _oxReader.Close();
                    _oxReader = null;
                }
                _isClosed = true;
            }
        }

        public bool Read() {            
            //optimisation possible : gérer un seul reader
            if (_isClosed) throw new Exception("reader closed!");
            _xlsxRow.Clear();
            if (!_oxReader.EOF && _oxReader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Row)) {
                ++_ncLine;
                OpenXmlReader le = OpenXmlReader.Create(_oxReader.LoadCurrentElement());
                // ----------
                // on duplique le code pour optimiser la lecture du numero de la ligne Excel
                while (!le.EOF && le.Read()) {
                    while (!le.EOF && le.ElementType != typeof(DocumentFormat.OpenXml.Spreadsheet.Cell)) le.Read(); //on se positionne sur le IsEndElement = false
                    if (!le.EOF) {
                        if (le.HasAttributes) {
                            OpenXmlAttribute oxa = (from a in le.Attributes where a.LocalName == "r" select a).FirstOrDefault();
                            if (oxa != null) {
                                _ncExcelLine = GetRowNumberFromCellId(oxa.Value);
                            }
                        }
                        PutObjectFromSheetCellIntoList(le, _xlsxRow);
                        while (!le.EOF && le.ElementType != (typeof(DocumentFormat.OpenXml.Spreadsheet.Cell))) le.Read(); //on se positionne sur le IsEndElement = true
                    }
                    break;
                }
                // ----------
                while (!le.EOF && le.Read()) {
                    while (!le.EOF && le.ElementType != typeof(DocumentFormat.OpenXml.Spreadsheet.Cell)) le.Read(); //on se positionne sur le IsEndElement = false
                    if (!le.EOF) {
                        PutObjectFromSheetCellIntoList(le, _xlsxRow);
                        while (!le.EOF && le.ElementType != (typeof(DocumentFormat.OpenXml.Spreadsheet.Cell))) le.Read(); //on se positionne sur le IsEndElement = true
                    }
                }
                le.Close();
                _oxReader.ReadNextSibling();

                _currentRowMaxLength = _xlsxRow.Count ;
                if (_currentRowMaxLength > _rowMaxLength)
                    _rowMaxLength = _currentRowMaxLength;

                return true;
            }
            return false;
        }
        
        public void Dispose() {
            if ( !_isDisposed ) {
                this.Close();
                _isDisposed = true;
            }
        }

            #endregion

            #region properties

        public object this[int i] {
            get {
                //gestion des colonnes vides à droite
                if (i >= _currentRowMaxLength && i < _rowMaxLength)
                    return null;
                return _xlsxRow[i]; 
            }
        }

        public int FieldCount {
            get { return _rowMaxLength; }
        }

        public bool IsClosed {
            get { return _isClosed; }
        }

            #endregion

            #region NotImplemented

        public int Depth {
            get { throw new NotImplementedException(); }
        }

        public DataTable GetSchemaTable() {
            throw new NotImplementedException();
        }

        public bool NextResult() {
            throw new NotImplementedException();
        }

        public int RecordsAffected {
            get { throw new NotImplementedException(); }
        }

        public bool GetBoolean(int i) {
            throw new NotImplementedException();
        }

        public byte GetByte(int i) {
            throw new NotImplementedException();
        }

        public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length) {
            throw new NotImplementedException();
        }

        public char GetChar(int i) {
            throw new NotImplementedException();
        }

        public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length) {
            throw new NotImplementedException();
        }

        public IDataReader GetData(int i) {
            throw new NotImplementedException();
        }

        public string GetDataTypeName(int i) {
            throw new NotImplementedException();
        }

        public DateTime GetDateTime(int i) {
            throw new NotImplementedException();
        }

        public decimal GetDecimal(int i) {
            try {
                return Decimal.Parse(this[i].ToString());
            } catch {
                return Decimal.Parse(this[i].ToString().Replace('.', ','));
            }
        }

        public double GetDouble(int i) {
            throw new NotImplementedException();
        }

        public Type GetFieldType(int i) {
            throw new NotImplementedException();
        }

        public float GetFloat(int i) {
            throw new NotImplementedException();
        }

        public Guid GetGuid(int i) {
            throw new NotImplementedException();
        }

        public short GetInt16(int i) {
            throw new NotImplementedException();
        }

        public int GetInt32(int i) {
            return Int32.Parse(this[i].ToString());
        }

        public long GetInt64(int i) {
            throw new NotImplementedException();
        }

        public string GetName(int i) {
            throw new NotImplementedException();
        }

        public int GetOrdinal(string name) {
            throw new NotImplementedException();
        }

        public string GetString(int i) {
            throw new NotImplementedException();
        }

        public object GetValue(int i) {
            throw new NotImplementedException();
        }

        public int GetValues(object[] values) {
            throw new NotImplementedException();
        }

        public bool IsDBNull(int i) {
            throw new NotImplementedException();
        }

        public object this[string name] {
            get { throw new NotImplementedException(); }
        }        

            #endregion

        #endregion

        #region IEnumerable<object> Membres

        public IEnumerator<object> GetEnumerator () {
            return _xlsxRow.GetEnumerator();
        }

        #endregion

        #region IEnumerable Membres

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator () {
            return _xlsxRow.GetEnumerator();
        }

        #endregion

        #region Utility methods

            #region Private

        private Object PutObjectFromSheetCellIntoList (OpenXmlReader le, List<Object> lo) {
            Boolean isString = false;
            Object o = null;
            DocumentFormat.OpenXml.Spreadsheet.CellFormat cf = null;
            Int32 colId = 0;

            // ==================================
            // calcul de l'objet
            if ( le.HasAttributes ) {
                foreach (var x in le.Attributes) {
                    switch ( x.LocalName ) {
                        case "r" :
                            colId = GetColIdFromCellId(x.Value);
                            break;
                        case "s" :
                            cf = _esConnection.CellFormats[Int32.Parse(x.Value)];
                            break;
                        case "t" :
                            if (x.Value == "s") isString = true;
                            break;
                    }
                }
            }
            while ( 
                    (!le.EOF && le.ElementType != typeof(DocumentFormat.OpenXml.Spreadsheet.CellValue) ) &&
                    !(le.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Cell) && le.IsEndElement == true) // on est dans une cellule vide, on ne peut pas se contenter du IsEndElement car Cell peut contenir autre chose qu'un CellValue
                ) 
                le.Read();
            if ( !le.EOF && !le.IsEndElement ) {
                String text = le.GetText();
                //gestion du format de la cellule.
                if ( cf != null ) {
                    o = expandFormatOnText(cf, text, isString);                    
                    // cas par défaut : cellule sans format.
                } else {
                    if ( isString ) {
                        o = _esConnection.SharedStringTableAsList[Int32.Parse(text)];
                        //pour l'histoire en mode full xpath
                        //XElement xo = _sharedStringTable.XPathSelectElement("./def:si[position()=" + (Int32.Parse(text) + 1).ToString() + "]/def:t[1]", _nsmanager);
                        //if (xo != null) text = xo.Value; else text = String.Empty;
                    } else 
                        o = text;
                }
            } else {
                //cas de la cellule vide
                o = String.Empty;
            }

            // ==================================
            // ajout de l'objet à la liste
            if ( colId == 0 ) {
                lo.Add(o);
            } else {
                //gestion des colonnes vides à gauche.
                while ( lo.Count < colId) lo.Add(null);
                lo.Add(o);
            }

            return o;
        }

        private Object expandFormatOnText(DocumentFormat.OpenXml.Spreadsheet.CellFormat cf, string text, Boolean isString = false) {
            if (cf.NumberFormatId >= 14 && cf.NumberFormatId <= 22)
                //on a une date
                return ExcelSaxDataReader.BaseDate.AddDays(Int32.Parse(text));
            else
                if (isString)
                    return _esConnection.SharedStringTableAsList[Int32.Parse(text)];
                else
                    return text;
        }

            #endregion

            #region Public

        /// <summary>
        /// Calcul l'indice en base 0 de la colonne d'une cellule Excel
        /// </summary>
        /// <param name="cellId">une chaîne de caractère correspondant à un identifiant de cellule excel.</param>
        /// <returns></returns>
        public static Int32 GetColIdFromCellId (String cellId) {
            Int32 ci = 0;
            Char[] a = cellId.ToCharArray();
            
            /*fixed (Char* pa = a) {
                Char* pc = pa;
                while (*pc >= 'A' && *pc <= 'Z') {
                    ci = ci * 26 + *pc - 'A' + 1;
                    pc++;
                }
            }*/

            foreach (Char c in a) {                
                if ( c >= 'A' && c <= 'Z' ) {
                //if (!Char.IsDigit(c)) {
                    ci = ci * 26 + c - 'A' + 1;
                } else break;
            }

            return ci - 1;
        }

        public static Int32 GetRowNumberFromCellId(String cellId) {
            Int32 ci = 0;
            Char[] a = cellId.ToCharArray();

            foreach (Char c in a) {
                if (c >= '0' && c <= '9') {
                    ci = ci * 10 + c - '0';
                }
            }

            return ci - 1;
        }

            #endregion

        #endregion
    }
}
